Solution: Specify Values in Data

There’s a better solution to restrict values in a column: creating a lookup table with one row for each value that we allow in the Bugs.status column and then declaring a foreign key constraint on Bugs.status to reference the new table.

When we insert or update a row in the Bugs table, we must use a status value that exists in the BugStatus table. Although this enforces the status values like ENUM or a check constraint, there are also several ways this solution offers more flexibility.

Querying the set of values#

The set of permitted values is now stored in data, not metadata, as it was with the ENUM data type. We can query data values from a lookup table with SELECT, just like any other table. This makes it much easier to retrieve the set of values as a data set to present in our user interface. We can even sort the set of values the user can choose from.

Updating the values in the lookup table#

When we use a lookup table, we can add a value to the set with an ordinary INSERT statement. We can make a change like this without interrupting access to the table. We don’t need to redefine any columns, schedule a downtime, or perform an ETL operation. We also don’t need to know the current set of values in the lookup table to add or remove a value.

Let’s see what happens when we press “RUN” to execute the code written in the following playground to retrieve the content in the BugStatus table.

We can also rename a value easily if we declare the foreign key with the ON UPDATE CASCADE option.

Let’s run the code in the following widget to see the output.

We did not have BOGUS as a value in the BugStatus table. Instead, we first added it and then changed its value.

Supporting obsolete values#

We can’t DELETE a row from the lookup table if it’s referenced by a row in Bugs. The foreign key on the status column enforces referential integrity, so the value must exist in the lookup table.

However, we can add another attribute column to the lookup table to designate some values as obsolete. This allows us to maintain historical data in the Bugs.status column while distinguishing between the obsolete values and values that are eligible to appear in our user interface.

We use UPDATE instead of DELETE to make a value obsolete:

Then, when we retrieve the set of values to show in a user interface for users to pick, we restrict the query to status values that are ACTIVE:

This gives us more flexibility than an ENUM or a check constraint because those solutions don’t support extra attributes per value.

Portability is easy#

Unlike the ENUM data type, check constraints, domains, or UDTs, the lookup table solution relies only on the standard SQL feature of declarative referential integrity using foreign key constraints. This makes the solution more portable.

We can also keep a virtually unlimited number of values in our lookup table since we store each value on a separate row.